const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, Op, QueryTypes} = require("../../models/db");
const tools = require("../../tools/tool");
// 交款核对
router.post('/bill/getTodaySk', async ctx => {
    let body = ctx.request.body
    let tid = ctx.user.tenantId
    let billDate = body.billDate
    let uid = body.uid
    let sql = `
        SELECT a.*, b.name accName
        from (SELECT accId, sum(accAmount) amount
              from (SELECT a.accId, sum(isnull(a.accAmount, 0)) accAmount
                    from invoice a
                    where a.tenantId = ${tid}
                      and a.billType = 'RECEIPT'
                      and a.cby = ${uid}
                      and a.doId = ${uid}
                      and a.billState = 'draft'
                      and a.billDate = '${billDate}'
                      and isnull(accId, 0) <> 0
                    GROUP BY accId
                    union all
                    SELECT a.accId1 accId, sum(isnull(a.accAmount1, 0)) accAmount
                    from invoice a
                    where a.tenantId = ${tid}
                      and a.billType = 'RECEIPT'
                      and a.cby = ${uid}
                      and a.doId = ${uid}
                      and a.billState = 'draft'
                      and a.billDate = '${billDate}'
                      and isnull(accId1, 0) <> 0
                    GROUP BY accId1) a
              GROUP BY accId) a
                 left join category b on a.accId = b.id
     `
    let acc = await db.query(sql, {type: QueryTypes.SELECT})
    let ss = 0 // 实收
    acc.map(row => {
        ss = tool.xe.add(tool.xe.toNumber(row.amount), ss)
        row.amount = tool.xe.toFixed(row.amount, 2)
    })
    // 商户数量
    let shsql = `
        select count(*) total
        from (SELECT buId
              from invoice a
              where a.tenantId = ${tid}
                and a.billType = 'RECEIPT'
                and a.cby = ${uid}
                and a.doId = ${uid}
                and a.billState='draft'
                and a.billDate = '${billDate}'
              GROUP BY buId) a
    `
    let shs = await db.query(shsql, {type: QueryTypes.SELECT, plain: true})
    let pjsql = `
        SELECT count(*) total 
        from 
        (
         SELECT billId from 
         (
         SELECT a.id from invoice a where 
           a.tenantId=${tid} and a.billType='RECEIPT' and a.cby=${uid} and a.doId=${uid} and a.billState='draft' and a.billDate='${billDate}'
         ) a inner join invoice_receipt b on a.id=b.iid GROUP BY billId
         ) a 
    `
    let pjs = await db.query(pjsql, {type: QueryTypes.SELECT, plain: true})
    let yhsql = `
     SELECT sum(isnull(yhAmount,0)) total from invoice a where 
    a.tenantId=${tid} and a.billType='RECEIPT' and a.cby=${uid}  and a.doId=${uid}  and a.billState='draft'  and a.billDate='${billDate}'
    `
    // 优惠合计
    let yh = await db.query(yhsql, {type: QueryTypes.SELECT, plain: true})
    // 本次合计
    let bcsql = `
        SELECT sum(totalAmount) total from invoice a where 
        a.tenantId=${tid} and a.billType='RECEIPT' and a.cby=${uid} and a.doId=${uid} and a.billState='draft'  and a.billDate='${billDate}'
    `
    let bc = await db.query(bcsql, {type: QueryTypes.SELECT, plain: true})
    let yssql = `
      SELECT sum(isnull(b.leftAmount,0)) total from invoice a inner join invoice_receipt b on a.id=b.iid where 
        a.tenantId=${tid} and a.billType='RECEIPT' and a.cby=${uid} and a.doId=${uid} and a.billState='draft'  and a.billDate='${billDate}'
    `
    let ys = await db.query(yssql, {type: QueryTypes.SELECT, plain: true})
    return ctx.suc('', {
        accList: acc,
        ss: tool.xe.toFixed(ss, 2),
        shs: shs ? shs.total : '',
        pjs: pjs ? pjs.total : '',
        yh: yh ? tool.xe.toFixed(yh.total, 2) : '',
        bc: bc ? tool.xe.toFixed(bc.total, 2) : '',
        ys: bc ? tool.xe.toFixed(ys.total, 2) : '',
    })

})





module.exports = router;